import pandas as pd
import requests
from openpyxl import Workbook
from pathlib import Path

# 读取CSV文件中的网点数据
csv_file_path = "E:\\Program Files\\Pythonproject\\pythonProject1\\bjsj.csv"
try:
    destinations_df = pd.read_csv(csv_file_path)
except Exception as e:
    print(f"读取CSV文件失败: {e}")
    destinations_df = pd.DataFrame()  # 如果失败，使用空DataFrame

# 高德API模板
api_template = "https://restapi.amap.com/v5/direction/driving?key=2074e3830cf12c789ed409e1517eed6d&origin={origin}&destination={destination}"

# 起点列表
start_points = [
    ("中诚大厦", "114.277158,30.592578"),
    ("良友大厦", "114.273801,30.59283"),
    ("武银大厦", "114.273599,30.596733")
]

# 定义fetch_route_info函数用于获取API数据
def fetch_route_info(origin, destination):
    api_url = api_template.format(origin=origin, destination=destination)
    try:
        response = requests.get(api_url, timeout=10)
        response.raise_for_status()
        data = response.json()
        # 确保返回的数据包含有效的路线信息
        if data.get('status') == '1' and data.get('route', {}).get('paths'):
            paths = data['route']['paths']
            return {
                'count': len(paths),
                'distance': min(paths, key=lambda p: p.get('distance', 0))['distance'] if paths else '未知'
            }
        else:
            print(f"警告: 没有找到有效的路线信息 - {api_url}")
    except requests.exceptions.RequestException as e:
        print(f"请求出错: {e}")
    return None

# 写入数据到Excel并保存
def save_data_to_excel(start_name, start_coordinate, destinations_df):
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = start_name.replace(" ", "_")  # 替换空格以符合文件名规则

    sheet.append(["起点", "起点坐标", "目的地", "目的地坐标", "行车方案数", "最短距离"])
    for index, row in destinations_df.iterrows():
        destination_name = row['name']
        destination_coordinate = row['location']
        route_info = fetch_route_info(start_coordinate, destination_coordinate)
        if route_info:
            count = route_info.get('count', '无数据')
            distance = route_info.get('distance', '无数据')
            sheet.append([start_name, start_coordinate, destination_name, destination_coordinate, count, distance])
        else:
            sheet.append([start_name, start_coordinate, destination_name, destination_coordinate, '无数据', '无数据'])

    # 保存Excel文件
    excel_path = Path(f"{start_name}_行车方案数据.xlsx")
    workbook.save(excel_path)
    print(f"{start_name} 的数据已导出到 {excel_path}")

# 主逻辑
for start_name, start_coordinate in start_points:
    save_data_to_excel(start_name, start_coordinate, destinations_df)

print("所有起点的Excel文件已生成。")